#coding=utf-8
import unittest
import os
import sys
import time
import datetime
import shutil
import pymysql

ip_address = '192.168.101.14'
port = '3306'
user_name = 'root'
password = 'egova'
datebases_name = 'hzcg1101'
base_dir = 'd:/test12341/'


class mySqlImport():

    def export_sql_file(self):

        connectInfor = 'mysqldump -h 192.168.101.14 -P 3306 -uroot -pegova'
        base_command = 'mysqldump -h '
        databases = 'hzcg1101'
        sql_size_all = 0
        db_info = ip_address + ' -P ' + port + ' -u' + user_name + ' -p' + password + ' ' + datebases_name
        print db_info
        mySqlImportInstance = mySqlImport()
        table_list = mySqlImportInstance.get_table_list
        StartCycle = datetime.datetime.now()

        for i in range(0,len(table_list)):
            table_name = table_list[i]
            sql_file = base_dir + datebases_name + '/' + table_name+'.sql'
            command = connectInfor + ' '+ databases + ' --default-character-set=gbk --set-gtid-purged=off -R ' + table_name + ' > ' + sql_file
            command2 = base_command + db_info + ' ' + table_name + ' > ' + sql_file

            start = datetime.datetime.now()
            os.system(command2)
            end = datetime.datetime.now()
            sql_size = os.path.getsize(sql_file) / 1024 / 1024
            sql_size_all = sql_size_all + sql_size
            useTime = (end-start).seconds
            print 'sqlname:'+ table_name +'; size= ' + str(sql_size) + 'M; useTime=' + str(useTime) + 's '

        endCycle = datetime.datetime.now()
        use_time_all = (endCycle - StartCycle).seconds
        print 'AllFileSize= ' + str(sql_size_all) + 'M; useTime=' + str(use_time_all) + 's '
        return 0

    @property
    def get_table_list(self):

        # dbConn1 = pymysql.connect(host='192.168.101.14',user='root',passwd='egova',db='hzcg1101',port=3306)
        dbConn = pymysql.connect(host=ip_address, user=user_name, passwd=password, db=datebases_name, port=3306)
        sqlfile = 'show tables;'
        dbCur = dbConn.cursor()
        dbCur.execute(sqlfile)
        table_list = dbCur.fetchall()
        table_num = len(table_list)
        dbCur.close()
        dbConn.close()
        table_name_list = []
        print table_num

        for i in range(0,table_num):
            table_name_ex = table_list[i]
            end_num = str(table_name_ex).find('\',)')
            table_name = str(table_name_ex)[2:end_num]
            # print str(table_name_ex)[2:end_num]
            table_name_list.append(table_name)
        print table_name_list
        return table_name_list

    def get_export_db_info(self):
        global ip_address,datebases_name,user_name,password
        print u'请输入导出的服务器ip地址'
        ip_address = raw_input()
        print u'请输入导入的数据库实例名称'
        datebases_name = raw_input()
        print  u'请输入连接数据库的用户名'
        user_name = raw_input()
        print  u'请输入连接数据库的密码'
        password = raw_input()
        print  u'请输入导出文件夹路径'
        base_dir = raw_input()
        print ip_address + ' ' + datebases_name + ' ' + user_name + ' ' + password

    def floder_operate(self):
        if os.path.exists(base_dir):
            shutil.rmtree(base_dir)
        os.mkdir(base_dir)
        sql_file_path = base_dir + datebases_name
        os.mkdir(sql_file_path)

if __name__ == '__main__':
    test = mySqlImport()
    test.get_export_db_info()
    print u'请确认信息是否正确，正确输入Y 进行数据库导出'
    makeSure = raw_input()
    if makeSure == 'Y':
        test.floder_operate()
        test.export_sql_file()
        # print ip_address + ' ' + datebases_name + ' ' + user_name + ' ' + password
    else:
        print u'退出了1。。。。。'
        exit()

    unittest.main()
